package cn.iocoder.yudao.module.wengfu.framework.excel.core.util;

import cn.idev.excel.write.handler.SheetWriteHandler;
import cn.idev.excel.write.handler.context.SheetWriteHandlerContext;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.module.wengfu.controller.admin.consignmentprocessingreport.vo.ConsignmentProcessingReportPageReqVO;
import cn.iocoder.yudao.module.wengfu.dal.dataobject.consignmentprocessingreport.ConsignmentProcessingReportDO;
import cn.iocoder.yudao.module.wengfu.framework.excel.core.util.wengfu.FormulaCalculationService;
import cn.iocoder.yudao.module.wengfu.service.consignmentprocessingreport.ConsignmentProcessingReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 磷矿石报表 Sheet 写入处理器
 * <p>
 * 负责：
 * 1. 创建统一的单元格样式；
 * 2. 构建表头、数据行以及合并单元格；
 * 3. 收集、计算并替换公式（四舍五入保留两位小数）；
 * 4. 为所有合并区域统一设置边框。
 * <p>
 * 代码实现保持原有业务逻辑不变，仅对冗余代码进行清理并补充注释，以提升可读性。
 */
@Slf4j
@Component
public class PhosphorusOreSheetWriteHandler implements SheetWriteHandler {

    @Autowired
    private ConsignmentProcessingReportService consignmentProcessingReportService;

    /** 动态数据（来自 Service） */
    private Map<String, BigDecimal> data;

    /** 当前 Sheet 名称（外部可通过 setSheetName 注入） */
    private String sheetName;

    /** 公式计算服务（构造函数中初始化） */
    private final FormulaCalculationService formulaCalculator;

    /** 统一居中、带边框的单元格样式 */
    private CellStyle centerStyle;

    /** 缓存所有单元格的计算结果（用于后续公式替换） */
    private final Map<String, Object> cellValues = new ConcurrentHashMap<>();

    /** 记录所有公式及其所在单元格地址 */
    private final Map<String, String> formulaMap = new ConcurrentHashMap<>();

    List<ConsignmentProcessingReportDO> list;

    /** 构造器：仅执行一次，初始化公式计算服务 */
    public PhosphorusOreSheetWriteHandler() {
        this.formulaCalculator = new FormulaCalculationService();
    }

    /** 外部调用设置 Sheet 名称 */
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    /** Sheet 创建完成后执行的核心流程 */
    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        try {
            Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
            Sheet sheet = getSheetFromContext(context, workbook);

            // 1️⃣ 创建统一的居中样式（含黑色细线边框）
            createStyle(workbook);

            if (sheet == null) {
                log.info("无法获取 Sheet，跳过表格初始化");
                return;
            }

            // 2️⃣ 从业务 Service 获取最新数据
//            data = consignmentProcessingReportService.UpdateConsignmentProcessingReport();

            System.out.println("开始创建完整表格结构，Sheet: " + sheet.getSheetName());

            // 3️⃣ 构建完整的表格结构（表头 + 数据行 + 合并单元格）
            setupCompleteTable(workbook, sheet, sheetName);

//            // 4️⃣ 收集所有公式
//            collectAllFormulas(sheet);
//
//            // 5️⃣ 计算公式并缓存四舍五入后的结果
//            calculateAndSaveFormulas(workbook);
//
//            // 6️⃣ 用计算结果替换公式单元格（彻底去除公式）
//            replaceFormulasWithValues(workbook);

            // 7️⃣ 再次确保所有合并区域的边框完整
            applyBordersToAllMergedRegions(sheet);
        } catch (Exception e) {
            log.error("初始化表格时异常", e);
        }
    }

    /** 创建统一的居中样式（包含字体、对齐方式、黑色细线边框） */
    private void createStyle(Workbook workbook) {
        centerStyle = workbook.createCellStyle();
        centerStyle.setAlignment(HorizontalAlignment.CENTER);
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        centerStyle.setFont(font);

        // 黑色细线边框
        centerStyle.setBorderTop(BorderStyle.THIN);
        centerStyle.setBorderBottom(BorderStyle.THIN);
        centerStyle.setBorderLeft(BorderStyle.THIN);
        centerStyle.setBorderRight(BorderStyle.THIN);
        centerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        centerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        centerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        centerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    }

    /** 收集工作表中所有公式并记录到 formulaMap（key 为单元格地址） */
    private void collectAllFormulas(Sheet sheet) {
        formulaMap.clear();
        for (Row row : sheet) {
            if (row == null) continue;
            for (Cell cell : row) {
                if (cell == null) continue;
                if (cell.getCellType() == CellType.FORMULA) {
                    String address = getCellAddress(cell);
                    formulaMap.put(address, cell.getCellFormula());
                }
            }
        }
        log.info("收集到 {} 个公式", formulaMap.size());
    }

    /**
     * 将任意对象（Number、String、BigDecimal 等）保留两位小数并四舍五入。
     *
     * @param value 待处理的数值对象，null 时返回 0.0
     * @return 四舍五入后保留两位小数的 double
     */
    private double round2(Object value) {
        if (value == null) {
            return 0.0;
        }
        BigDecimal bd;
        if (value instanceof Number) {
            bd = new BigDecimal(((Number) value).doubleValue());
        } else {
            try {
                bd = new BigDecimal(value.toString());
            } catch (Exception e) {
                return 0.0;
            }
        }
        return bd.setScale(2, RoundingMode.HALF_UP).doubleValue();
    }

    /** 计算所有已收集的公式并将四舍五入后的结果保存到 cellValues 中 */
    private void calculateAndSaveFormulas(Workbook workbook) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        for (Map.Entry<String, String> entry : formulaMap.entrySet()) {
            String address = entry.getKey();                     // 例如 "D8"
            CellReference ref = new CellReference(address);
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                log.warn("未找到 sheet {}，公式 {} 跳过", sheetName, address);
                continue;
            }
            Row row = sheet.getRow(ref.getRow());
            if (row == null) {
                log.warn("未找到行 {}，公式 {} 跳过", ref.getRow(), address);
                continue;
            }
            Cell cell = row.getCell(ref.getCol());
            if (cell == null) {
                log.warn("未找到单元格 {}，公式 {} 跳过", address, entry.getValue());
                continue;
            }

            CellValue cv = evaluator.evaluate(cell);
            Object rawValue = null;
            if (cv != null) {
                switch (cv.getCellType()) {
                    case NUMERIC:
                        rawValue = cv.getNumberValue();
                        break;
                    case STRING:
                        rawValue = cv.getStringValue();
                        break;
                    case BOOLEAN:
                        rawValue = cv.getBooleanValue();
                        break;
                    default:
                        rawValue = null;
                }
            }

            if (rawValue instanceof Number) {
                double rounded = round2(rawValue);
                cellValues.put(address, rounded);
            } else if (rawValue != null) {
                cellValues.put(address, rawValue);
            } else {
                cellValues.put(address, 0.0);
                log.warn("公式 {} 计算结果为 null，已使用 0.0 兜底", address);
            }
        }
        log.info("成功计算并保存 {} 条公式（已四舍五入）", cellValues.size());
    }

    /** 将所有公式单元格替换为已四舍五入的数值，并关闭强制重新计算 */
    private void replaceFormulasWithValues(Workbook workbook) {
        workbook.setForceFormulaRecalculation(false);

        for (Sheet sheet : workbook) {
            for (Row row : sheet) {
                if (row == null) continue;
                for (Cell cell : row) {
                    if (cell == null) continue;
                    if (cell.getCellType() == CellType.FORMULA) {
                        String address = getCellAddress(cell);
                        Object value = cellValues.get(address);
                        if (value != null) {
                            CellStyle originalStyle = cell.getCellStyle();
                            // 创建一个新单元格覆盖旧单元格（避免直接清除公式的复杂操作）
                            Cell newCell = row.createCell(cell.getColumnIndex(), CellType.BLANK);
                            if (value instanceof Number) {
                                newCell.setCellValue(round2(value));
                            } else if (value instanceof String) {
                                newCell.setCellValue((String) value);
                            } else if (value instanceof Boolean) {
                                newCell.setCellValue((Boolean) value);
                            } else {
                                newCell.setCellValue(String.valueOf(value));
                            }
                            newCell.setCellStyle(originalStyle);
                        }
                    }
                }
            }
        }
        log.info("已完成公式替换为四舍五入后的数值（公式已清除）");
    }

    /** 获取单元格的 A1 形式地址（如 D8） */
    private String getCellAddress(Cell cell) {
        return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
    }

    /** 从上下文中获取当前 Sheet（兼容不同实现） */
    private Sheet getSheetFromContext(SheetWriteHandlerContext context, Workbook workbook) {
        try {
            // 1️⃣ 通过反射尝试直接获取 Sheet
            return (Sheet) context.getClass().getMethod("getSheet").invoke(context);
        } catch (Exception e1) {
            try {
                // 2️⃣ 通过 WriteSheetHolder 获取
                return context.getWriteSheetHolder().getSheet();
            } catch (Exception e2) {
                // 3️⃣ 根据 Sheet 名称获取或新建
                String name = context.getWriteSheetHolder().getSheetName();
                Sheet sheet = workbook.getSheet(name);
                return sheet != null ? sheet : workbook.createSheet(name);
            }
        }
    }

    /** 完整表格构建入口：创建行、表头、数据行以及合并单元格 */
    private void setupCompleteTable(Workbook workbook, Sheet sheet, String sheetName)
            throws InvocationTargetException, NoSuchMethodException, IllegalAccessException {
        createAllRows(sheet, 60);               // 预创建 0\~60 行
        setupHeaderRows(workbook, sheet);       // 表头
        setupAllDataRows(sheet);                // 数据行
        applyAllMergeRegions(sheet);            // 合并单元格
    }

    /** 创建指定数量的空行（包括 0 行） */
    private void createAllRows(Sheet sheet, int maxRows) {
        for (int i = 0; i <= maxRows; i++) {
            getOrCreateRow(sheet, i);
        }
    }

    /** 表头行的创建与样式设置 */
    private void setupHeaderRows(Workbook workbook, Sheet sheet) {
        System.out.println("设置表头行...");

        // 列宽设置（单位：1/256 字符宽度）
        sheet.setColumnWidth(0, 8 * 256);   // 序号
        sheet.setColumnWidth(1, 25 * 256);  // 产品名称
        sheet.setColumnWidth(2, 25 * 256);  // 品级
        sheet.setColumnWidth(3, 20 * 256);  // 本月-平均
        sheet.setColumnWidth(4, 20 * 256);  // 本月-数量(t)
        sheet.setColumnWidth(5, 20 * 256);  // 本年累计-平均
        sheet.setColumnWidth(6, 20 * 256);  // 本年累计-数量(t)
        sheet.setColumnWidth(7, 20 * 256);  // 去年同期累计-平均
        sheet.setColumnWidth(8, 20 * 256);  // 去年同期累计-数量(t)
        sheet.setColumnWidth(9, 15 * 256);  // 空列（间隔）
        sheet.setColumnWidth(10, 20 * 256); // 年累计-平均
        sheet.setColumnWidth(11, 20 * 256); // 年累计-数量(t)

        CellStyle blackBorderStyle = createBlackBorderStyle(workbook);
        CellStyle titleStyle = createTitleStyle(workbook);

        // 第 1 行：标题（合并 0\~8 列）
        Row titleRow = getOrCreateRow(sheet, 0);
        titleRow.setHeightInPoints(40);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("委托加工及购进矿石质量报表");
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
        setMergedRegionBorder(sheet, 0, 0, 0, 8, blackBorderStyle);

        // 第 2 行：第二层表头
        Row secondHeaderRow = getOrCreateRow(sheet, 1);
        secondHeaderRow.setHeightInPoints(20);
        String[] headers2 = {"序号", "产品名称", "品级", "本月", "", "本年累计", "", "去年同期累计", " ", " ", "年 累 计(7月底数据源）"};
        for (int i = 0; i < headers2.length; i++) {
            Cell cell = secondHeaderRow.createCell(i);
            cell.setCellValue(headers2[i]);
            cell.setCellStyle(blackBorderStyle);
        }

        // 第 3 行：第三层表头
        Row thirdHeaderRow = getOrCreateRow(sheet, 2);
        thirdHeaderRow.setHeightInPoints(20);
        String[] headers3 = {"", "", "", "平均", "数量（t)", "平均", "数量（t)", "平均", "数量（t)", "", "平均", "数量（t)"};
        for (int i = 0; i < headers3.length; i++) {
            Cell cell = thirdHeaderRow.createCell(i);
            cell.setCellValue(headers3[i]);
            cell.setCellStyle(blackBorderStyle);
        }

        // 合并单元格并设置完整边框
        setupMergedRegions(sheet, blackBorderStyle);
    }

    /** 合并表头区域并为每个合并单元格统一设置边框 */
    private void setupMergedRegions(Sheet sheet, CellStyle borderStyle) {
        // 垂直合并：序号、产品名称、品级（第 1\~3 行）
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));

        // 垂直合并：平均 / 数量 列（第 3\~4 行）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 11, 11));

        // 水平合并：本月、本年累计、去年同期累计、年累计（第 2 行）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 4));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 8));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 11));

        // 为所有合并区域设置完整边框
        setMergedRegionBorder(sheet, 1, 3, 0, 0, borderStyle);
        setMergedRegionBorder(sheet, 1, 3, 1, 1, borderStyle);
        setMergedRegionBorder(sheet, 1, 3, 2, 2, borderStyle);
        setMergedRegionBorder(sheet, 1, 1, 3, 4, borderStyle);
        setMergedRegionBorder(sheet, 1, 1, 5, 6, borderStyle);
        setMergedRegionBorder(sheet, 1, 1, 7, 8, borderStyle);
        setMergedRegionBorder(sheet, 1, 1, 10, 11, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 3, 3, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 4, 4, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 5, 5, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 6, 6, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 7, 7, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 8, 8, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 10, 10, borderStyle);
        setMergedRegionBorder(sheet, 2, 3, 11, 11, borderStyle);
    }

    /** 为指定合并区域的每个单元格创建独立的样式实例并设置边框 */
    private void setMergedRegionBorder(Sheet sheet, int firstRow, int lastRow,
                                       int firstCol, int lastCol, CellStyle borderStyle) {
        Workbook workbook = sheet.getWorkbook();
        for (int r = firstRow; r <= lastRow; r++) {
            Row row = sheet.getRow(r);
            if (row == null) row = sheet.createRow(r);
            for (int c = firstCol; c <= lastCol; c++) {
                Cell cell = row.getCell(c);
                if (cell == null) cell = row.createCell(c);
                CellStyle newStyle = workbook.createCellStyle();
                newStyle.cloneStyleFrom(borderStyle);
                cell.setCellStyle(newStyle);
            }
        }
    }

    /** 创建所有业务数据行（包括层级结构、公式、占位值等） */
    private void setupAllDataRows(Sheet sheet) throws InvocationTargetException,
            NoSuchMethodException, IllegalAccessException {
        System.out.println("设置所有数据行结构...");

        ConsignmentProcessingReportPageReqVO pageReqVO = new ConsignmentProcessingReportPageReqVO();
        pageReqVO.setPageSize(100);

        PageResult<ConsignmentProcessingReportDO> consignmentProcessingReportPage = consignmentProcessingReportService.getConsignmentProcessingReportPage(pageReqVO);
        list = consignmentProcessingReportPage.getList();

        // ---------- 受托加工磷矿石 ----------
        setupFirstCategoryRow(getOrCreateRow(sheet, 4), "一", "受托加工磷矿石", "P2O5(%)");
        setupFirstCategorySubRow(getOrCreateRow(sheet, 5), "R2O3(%)");
        setupFirstCategorySubRow(getOrCreateRow(sheet, 6), "SiO2(%)");

        // 1 黄磷公司
        setupHuanglinCompanyRow(getOrCreateRow(sheet, 7), "1", "黄磷公司", "P2O5(%)");
        setupHuanglinCompanySubRow(getOrCreateRow(sheet, 8), "R2O3(%)");
        setupHuanglinCompanySubRow(getOrCreateRow(sheet, 9), "SiO2(%)");

        // 1.1 a层矿
        setupALayerRow(getOrCreateRow(sheet, 10), "1.1", "a层矿", "P2O5(%)");
        setupALayerSubRow(getOrCreateRow(sheet, 11), "R2O3(%)");
        setupALayerSubRow(getOrCreateRow(sheet, 12), "SiO2(%)");

        // 其中 a 层矿返粉
        setupALayerReturnPowderRow(getOrCreateRow(sheet, 13), "其中a层矿返粉", "P2O5(%)");
        setupALayerReturnPowderSubRow(getOrCreateRow(sheet, 14), "R2O3(%)");
        setupALayerReturnPowderSubRow(getOrCreateRow(sheet, 15), "SiO2(%)");

        // 1.2 b层矿
        setupBLayerRow(getOrCreateRow(sheet, 16), "1.2", "b层矿", "P2O5(%)");
        setupBLayerSubRow(getOrCreateRow(sheet, 17), "R2O3(%)");
        setupBLayerSubRow(getOrCreateRow(sheet, 18), "SiO2(%)");

        // 其中划拨 b 层矿
        setupAssignedBLayerRow(getOrCreateRow(sheet, 19), "其中划拨b层矿", "P2O5(%)");
        setupAssignedBLayerSubRow(getOrCreateRow(sheet, 20), "R2O3(%)");
        setupAssignedBLayerSubRow(getOrCreateRow(sheet, 21), "SiO2(%)");

        // ---------- 购进磷矿石 ----------
        setupPurchaseOreRow(getOrCreateRow(sheet, 22), "二", "购进磷矿石", "P2O5(%)");
        setupPurchaseOreSubRow(getOrCreateRow(sheet, 23), "MgO(%)");
        setupPurchaseOreSubRow(getOrCreateRow(sheet, 24), "R2O3(%)");
        setupPurchaseOreSubRow(getOrCreateRow(sheet, 25), "SiO2(%)");

        // 1 自产矿石
        setupSelfProducedOreRow(getOrCreateRow(sheet, 26), "1", "自产矿石", "P2O5(%)");
        setupSelfProducedOreSubRow(getOrCreateRow(sheet, 27), "MgO(%)");
        setupSelfProducedOreSubRow(getOrCreateRow(sheet, 28), "R2O3(%)");
        setupSelfProducedOreSubRow(getOrCreateRow(sheet, 29), "SiO2(%)");

        // 2 外部矿石利用
        setupExternalOreRow(getOrCreateRow(sheet, 30), "2", "外部矿石利用", "P2O5(%)");
        setupExternalOreSubRow(getOrCreateRow(sheet, 31), "MgO(%)");
        setupExternalOreSubRow(getOrCreateRow(sheet, 32), "R2O3(%)");
        setupExternalOreSubRow(getOrCreateRow(sheet, 33), "SiO2(%)");

        // ---------- 双山坪公司 ----------
        setupShuangshanpingRow(getOrCreateRow(sheet, 34), "1", "双山坪公司", "P2O5(%)");
        setupShuangshanpingSubRow(getOrCreateRow(sheet, 35), "MgO(%)");
        setupShuangshanpingSubRow(getOrCreateRow(sheet, 36), "R2O3(%)");
        setupShuangshanpingSubRow(getOrCreateRow(sheet, 37), "SiO2(%)");

        // 1.1 自产矿石（双山坪公司）
        setupShuangshanpingSelfProducedRow(getOrCreateRow(sheet, 38), "1.1", "自产矿石", "P2O5(%)");
        setupShuangshanpingSelfProducedSubRow(getOrCreateRow(sheet, 39), "MgO(%)");
        setupShuangshanpingSelfProducedSubRow(getOrCreateRow(sheet, 40), "R2O3(%)");
        setupShuangshanpingSelfProducedSubRow(getOrCreateRow(sheet, 41), "SiO2(%)");

        // 1.2 外部矿石利用（双山坪公司）
        setupShuangshanpingExternalOreRow(getOrCreateRow(sheet, 42), "1.2", "外部矿石利用", "P2O5(%)");
        setupShuangshanpingExternalOreSubRow(getOrCreateRow(sheet, 43), "MgO(%)");
        setupShuangshanpingExternalOreSubRow(getOrCreateRow(sheet, 44), "R2O3(%)");
        setupShuangshanpingExternalOreSubRow(getOrCreateRow(sheet, 45), "SiO2(%)");

        // ---------- 有福公司 ----------
        setupYoufuCompanyRow(getOrCreateRow(sheet, 46), "2", "有福公司", "P2O5(%)");
        setupYoufuCompanySubRow(getOrCreateRow(sheet, 47), "MgO(%)");
        setupYoufuCompanySubRow(getOrCreateRow(sheet, 48), "R2O3(%)");
        setupYoufuCompanySubRow(getOrCreateRow(sheet, 49), "SiO2(%)");

        // 2.1 自产矿石（有福公司）
        setupYoufuSelfProducedRow(getOrCreateRow(sheet, 50), "2.1", "自产矿石", "P2O5(%)");
        setupYoufuSelfProducedSubRow(getOrCreateRow(sheet, 51), "MgO(%)");
        setupYoufuSelfProducedSubRow(getOrCreateRow(sheet, 52), "R2O3(%)");
        setupYoufuSelfProducedSubRow(getOrCreateRow(sheet, 53), "SiO2(%)");

        // 2.2 外部矿石利用（有福公司）
        setupYoufuExternalOreRow(getOrCreateRow(sheet, 54), "2.2", "外部矿石利用", "P2O5(%)");
        setupYoufuExternalOreSubRow(getOrCreateRow(sheet, 55), "MgO(%)");
        setupYoufuExternalOreSubRow(getOrCreateRow(sheet, 56), "R2O3(%)");
        setupYoufuExternalOreSubRow(getOrCreateRow(sheet, 57), "SiO2(%)");
    }

    /** 获取或创建指定索引的行 */
    private Row getOrCreateRow(Sheet sheet, int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        return (row != null) ? row : sheet.createRow(rowIndex);
    }

    // ==================== 单元格写入辅助方法 ====================

    /** 写入公式并记录到 formulaMap（同时处理 ISERROR → IFERROR 与外部引用） */
    private void setCellFormula(Row row, int columnIndex, String formula) {
        if (formula == null || formula.isEmpty()) return;

        // ① 替换 ISERROR 为 IFERROR
        String fixed = rewriteIsError(formula);
        // ② 将外部工作簿引用统一置为 0（避免跨文件错误）
        fixed = fixed.replaceAll("\\[\\d+\\][^!]+!", "0");

        Cell cell = row.createCell(columnIndex);
        cell.setCellFormula(fixed);
        cell.setCellStyle(centerStyle);

        // 记录公式地址
        String address = getCellAddress(cell);
        formulaMap.put(address, fixed);
    }

    /** 将 IF(ISERROR(...),0,(...)) 形式改写为 IFERROR(...,0) */
    private String rewriteIsError(String formula) {
        Pattern p = Pattern.compile(
                "IF\\s*\\(\\s*ISERROR\\s*\\(([^\\)]+)\\)\\s*,\\s*0\\s*,\\s*\\(([^\\)]+)\\)\\s*\\)",
                Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String inner = m.group(1).trim();
            String replacement = "IFERROR(" + inner + ",0)";
            m.appendReplacement(sb, replacement);
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /** 写入 double 类型数值（自动四舍五入保留两位小数） */
    private void setCellValue(Row row, int columnIndex, double value) {
        double rounded = round2(value);
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(rounded);
        // 为每个单元格创建独立的样式实例，避免样式共享导致的副作用
        CellStyle style = row.getSheet().getWorkbook().createCellStyle();
        style.cloneStyleFrom(centerStyle);
        cell.setCellStyle(style);
        cellValues.put(getCellAddress(cell), rounded);
    }

    /** 写入 String 类型数值（不做四舍五入） */
    private void setCellValue(Row row, int columnIndex, String value) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(value);
        CellStyle style = row.getSheet().getWorkbook().createCellStyle();
        style.cloneStyleFrom(centerStyle);
        cell.setCellStyle(style);
        cellValues.put(getCellAddress(cell), value);
    }

    /** 写入 BigDecimal 类型数值（转为 double 再四舍五入） */
    private void setCellValue(Row row, int columnIndex, BigDecimal value) {
        if (value != null) {
            setCellValue(row, columnIndex, value.doubleValue());
        } else {
            setCellValue(row, columnIndex, 0.0);
        }
    }

    // ==================== 各业务块的行构建 ====================

    // ---- 受托加工磷矿石 ----
    private void setupFirstCategoryRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);
        // 预留空白列
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    private void setupFirstCategorySubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    // ---- 黄磷公司 ----
    private void setupHuanglinCompanyRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    private void setupHuanglinCompanySubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    // ---- a层矿 ----
    private void setupALayerRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    private void setupALayerSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    // ---- a层矿返粉 ----
    private void setupALayerReturnPowderRow(Row row, String product, String grade) {
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    private void setupALayerReturnPowderSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    // ---- b层矿 ----
    private void setupBLayerRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    private void setupBLayerSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    // ---- 划拨 b层矿 ----
    private void setupAssignedBLayerRow(Row row, String product, String grade) {
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    private void setupAssignedBLayerSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        for (int i = 3; i <= 11; i++) {
            setCellValue(row, i, "");
        }
    }

    // ---- 购进磷矿石 ----
    private void setupPurchaseOreRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);


        setCellValue(row, 3, list.get(0).getMonthlyAverage());
        setCellValue(row, 4, list.get(0).getMonthlyQuantity());
        setCellValue(row, 5, list.get(0).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(0).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(0).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(0).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(0).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(0).getYearCumulativeQuantity());
    }

    private void setupPurchaseOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(1).getMonthlyAverage());
            setCellValue(row, 5, list.get(1).getMonthlyAverage());
            setCellValue(row, 7, list.get(1).getMonthlyAverage());
            setCellValue(row, 10, list.get(1).getMonthlyAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(2).getMonthlyAverage());
            setCellValue(row, 5, list.get(2).getMonthlyAverage());
            setCellValue(row, 7, list.get(2).getMonthlyAverage());
            setCellValue(row, 10, list.get(2).getMonthlyAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(3).getMonthlyAverage());
            setCellValue(row, 5, list.get(3).getMonthlyAverage());
            setCellValue(row, 7, list.get(3).getMonthlyAverage());
            setCellValue(row, 10, list.get(3).getMonthlyAverage());
        }
    }

    // ---- 自产矿石 ----
    private void setupSelfProducedOreRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(4).getMonthlyAverage());
        setCellValue(row, 4, list.get(4).getMonthlyQuantity());
        setCellValue(row, 5, list.get(4).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(4).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(4).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(4).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(4).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(4).getYearCumulativeQuantity());
    }

    private void setupSelfProducedOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(5).getMonthlyAverage());
            setCellValue(row, 5, list.get(5).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(5).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(5).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(6).getMonthlyAverage());
            setCellValue(row, 5, list.get(6).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(6).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(6).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(7).getMonthlyAverage());
            setCellValue(row, 5, list.get(7).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(7).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(7).getYearCumulativeAverage());
        }
    }

    // ---- 外部矿石利用 ----
    private void setupExternalOreRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(8).getMonthlyAverage());
        setCellValue(row, 4, list.get(8).getMonthlyQuantity());
        setCellValue(row, 5, list.get(8).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(8).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(8).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(8).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(8).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(8).getYearCumulativeQuantity());
    }

    private void setupExternalOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(9).getMonthlyAverage());
            setCellValue(row, 5, list.get(9).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(9).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(9).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(10).getMonthlyAverage());
            setCellValue(row, 5, list.get(10).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(10).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(10).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(11).getMonthlyAverage());
            setCellValue(row, 5, list.get(11).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(11).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(11).getYearCumulativeAverage());
        }
    }

    // ---- 双山坪公司 ----
    private void setupShuangshanpingRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(12).getMonthlyAverage());
        setCellValue(row, 4, list.get(12).getMonthlyQuantity());
        setCellValue(row, 5, list.get(12).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(12).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(12).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(12).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(12).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(12).getYearCumulativeQuantity());
    }

    private void setupShuangshanpingSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(13).getMonthlyAverage());
            setCellValue(row, 5, list.get(13).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(13).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(13).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(14).getMonthlyAverage());
            setCellValue(row, 5, list.get(14).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(14).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(14).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(15).getMonthlyAverage());
            setCellValue(row, 5, list.get(15).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(15).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(15).getYearCumulativeAverage());
        }
    }

    // ---- 双山坪公司 - 自产矿石 ----
    private void setupShuangshanpingSelfProducedRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(16).getMonthlyAverage());
        setCellValue(row, 4, list.get(16).getMonthlyQuantity());
        setCellValue(row, 5, list.get(16).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(16).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(16).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(16).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(16).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(16).getYearCumulativeQuantity());
    }

    private void setupShuangshanpingSelfProducedSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(17).getMonthlyAverage());
            setCellValue(row, 5, list.get(17).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(17).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(17).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(18).getMonthlyAverage());
            setCellValue(row, 5, list.get(18).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(18).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(18).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(19).getMonthlyAverage());
            setCellValue(row, 5, list.get(19).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(19).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(19).getYearCumulativeAverage());
        }
    }

    // ---- 双山坪公司 - 外部矿石利用 ----
    private void setupShuangshanpingExternalOreRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(20).getMonthlyAverage());
        setCellValue(row, 4, list.get(20).getMonthlyQuantity());
        setCellValue(row, 5, list.get(20).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(20).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(20).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(20).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(20).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(20).getYearCumulativeQuantity());
    }

    private void setupShuangshanpingExternalOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(21).getMonthlyAverage());
            setCellValue(row, 5, list.get(21).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(21).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(21).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(22).getMonthlyAverage());
            setCellValue(row, 5, list.get(22).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(22).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(22).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(23).getMonthlyAverage());
            setCellValue(row, 5, list.get(23).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(23).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(23).getYearCumulativeAverage());
        }
    }

    // ---- 有福公司 ----
    private void setupYoufuCompanyRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(24).getMonthlyAverage());
        setCellValue(row, 4, list.get(24).getMonthlyQuantity());
        setCellValue(row, 5, list.get(24).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(24).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(24).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(24).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(24).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(24).getYearCumulativeQuantity());
    }

    private void setupYoufuCompanySubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(25).getMonthlyAverage());
            setCellValue(row, 5, list.get(25).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(25).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(25).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(26).getMonthlyAverage());
            setCellValue(row, 5, list.get(26).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(26).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(26).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(27).getMonthlyAverage());
            setCellValue(row, 5, list.get(27).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(27).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(27).getYearCumulativeAverage());
        }
    }

    // ---- 有福公司 - 自产矿石 ----
    private void setupYoufuSelfProducedRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(28).getMonthlyAverage());
        setCellValue(row, 4, list.get(28).getMonthlyQuantity());
        setCellValue(row, 5, list.get(28).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(28).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(28).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(28).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(28).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(28).getYearCumulativeQuantity());
    }

    private void setupYoufuSelfProducedSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(29).getMonthlyAverage());
            setCellValue(row, 5, list.get(29).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(29).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(29).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(30).getMonthlyAverage());
            setCellValue(row, 5, list.get(30).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(30).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(30).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(31).getMonthlyAverage());
            setCellValue(row, 5, list.get(31).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(31).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(31).getYearCumulativeAverage());
        }
    }

    // ---- 有福公司 - 外部矿石利用 ----
    private void setupYoufuExternalOreRow(Row row, String seq, String product, String grade) {
        setCellValue(row, 0, seq);
        setCellValue(row, 1, product);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, list.get(32).getMonthlyAverage());
        setCellValue(row, 4, list.get(32).getMonthlyQuantity());
        setCellValue(row, 5, list.get(32).getYearlyCumulativeAverage());
        setCellValue(row, 6, list.get(32).getYearlyCumulativeQuantity());
        setCellValue(row, 7, list.get(32).getLastYearCumulativeAverage());
        setCellValue(row, 8, list.get(32).getLastYearCumulativeQuantity());
        setCellValue(row, 10, list.get(32).getYearCumulativeAverage());
        setCellValue(row, 11, list.get(32).getYearCumulativeQuantity());
    }

    private void setupYoufuExternalOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);
        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, list.get(33).getMonthlyAverage());
            setCellValue(row, 5, list.get(33).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(33).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(33).getYearCumulativeAverage());
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, list.get(34).getMonthlyAverage());
            setCellValue(row, 5, list.get(34).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(34).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(34).getYearCumulativeAverage());
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, list.get(35).getMonthlyAverage());
            setCellValue(row, 5, list.get(35).getYearlyCumulativeAverage());
            setCellValue(row, 7, list.get(35).getLastYearCumulativeAverage());
            setCellValue(row, 10, list.get(35).getYearCumulativeAverage());
        }
    }

    // ==================== 合并单元格与边框处理 ====================

    /** 应用业务层面的所有合并区域（包括序号、产品名称、数量等） */
    private void applyAllMergeRegions(Sheet sheet) {
        System.out.println("应用合并区域...");

        // 受托加工磷矿石
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 8, 8));

        // 黄磷公司
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 8, 8));

        // a层矿
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 8, 8));

        // a层矿返粉
        sheet.addMergedRegion(new CellRangeAddress(13, 15, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(13, 15, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(13, 15, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(13, 15, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(13, 15, 8, 8));

        // b层矿
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 8, 8));

        // 划拨b层矿
        sheet.addMergedRegion(new CellRangeAddress(19, 21, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(19, 21, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(19, 21, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(19, 21, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(19, 21, 8, 8));

        // 购进磷矿石
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 11, 11));

        // 自产矿石
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 11, 11));

        // 外部矿石利用（购进）
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 11, 11));

        // 双山坪公司
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 11, 11));

        // 双山坪公司 - 自产矿石
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 11, 11));

        // 双山坪公司 - 外部矿石利用
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 11, 11));

        // 有福公司
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 11, 11));

        // 有福公司 - 自产矿石
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 11, 11));

        // 有福公司 - 外部矿石利用
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 11, 11));

        // 完成后统一为所有合并区域设置边框
        applyBordersToAllMergedRegions(sheet);
    }

    /** 为所有合并区域统一设置边框（A、B 列以及前 4 行使用加粗样式） */
    private void applyBordersToAllMergedRegions(Sheet sheet) {
        Workbook workbook = sheet.getWorkbook();
        CellStyle boldBorderStyle = createBlackBorderStyle(workbook);   // 加粗
        CellStyle normalBorderStyle = createNormalBorderStyle(workbook); // 普通

        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            int firstRow = region.getFirstRow();
            int lastRow = region.getLastRow();
            int firstCol = region.getFirstColumn();
            int lastCol = region.getLastColumn();

            // 判断是否属于加粗范围（A/B 列或第 1\~4 行）
            boolean isBold = (firstCol == 0 || firstCol == 1) || (firstRow <= 3 && lastRow >= 0);
            CellStyle styleToApply = isBold ? boldBorderStyle : normalBorderStyle;

            setMergedRegionBorder(sheet, firstRow, lastRow, firstCol, lastCol, styleToApply);
        }
    }

    // ==================== 样式创建 ====================

    /** 普通（不加粗）黑色细线边框样式 */
    private CellStyle createNormalBorderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(false);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setWrapText(true);
        return style;
    }

    /** 加粗（黑体）黑色细线边框样式 */
    private CellStyle createBlackBorderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setWrapText(true);
        return style;
    }

    /** 标题行样式（加粗、字号 16） */
    private CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }
}
